# -*- coding:utf-8 -*-
__author__ = 'weikai'
import sys
reload(sys)
sys.setdefaultencoding('utf8')
import json
from my_dbUtil.sqlUtil import query, insert,update
from cjsonencoder import CJsonEncoder
from optionslist import car_type

def query_user_renewal(PLATE_NUMBER):
    # 查询一周内可用的数据用户数据
    sql_query = "SELECT COMPANY_ID, licenseNo,FLAG, vinNo, CCardDetail, brandName, insuredName, identifyNumber, insuredAddress, mobile, enrollDate, engineNo, CModelCde, NSeatNum,endDate FROM user_renewal WHERE licenseNo='%s'   AND vinNO!='' AND enrollDate!=''  AND CREATE_TIME >DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND FLAG='1' ORDER BY CREATE_TIME DESC LIMIT 1" % PLATE_NUMBER
    result = query(sql_query)
    if len(result)!=0:
        result=result[0]
        result={field: result.get(field, '').decode() for field in result if isinstance(result.get(field, ''), str)}
        return result
    else:
        return 0



def inser_user_renewal(dt):
    '''
    :param dt:{"licenseNo": "苏AN0Q83", "vinNo": "LVTDB14B7FC003044", "endDate": "2017-01-27", "CCardDetail": "K33", "brandName": "奇瑞SQR6451T217", "insuredName": "熊飞", "identifyNumber": "321183198903103659", "CUsageCde": "309001", "NNewPurchaseValue": "80910.00", "COMPANY_ID": "4", "insuredAddress": "句容市", "mobile": "13921551352", "enrollDate": "2015-01-29", "engineNo": "TAEM02094", "CModelCde": "QRAAFD0105", "NSeatNum": "5"}
    :return:
    '''
    del_user_renewal(dt.get("licenseNo",""))
    sql = "INSERT INTO user_renewal (COMPANY_ID, licenseNo,FLAG, vinNo, CCardDetail, brandName, insuredName, identifyNumber, insuredAddress, mobile, enrollDate, engineNo, CModelCde, NSeatNum,endDate) " \
        "VALUES('%s', '%s','%s','%s','%s', '%s','%s','%s','%s', '%s','%s','%s','%s', '%s','%s')" % (
            dt["COMPANY_ID"], dt["licenseNo"], "1", dt["vinNo"], dt.get("CCardDetail", ""), dt.get("brandName", ""), dt.get("insuredName", ""), dt.get("identifyNumber", ""), dt.get("insuredAddress", ""), dt.get("mobile", ""), dt.get("enrollDate", ""), dt.get("engineNo", ""), dt.get("CModelCde", ""), dt.get("NSeatNum", ""), dt.get("endDate", ""))
    out = insert(sql)
    return out


def del_user_renewal(PLATE_NUMBER):
    sql="DELETE FROM user_renewal WHERE licenseNo='%s'" % PLATE_NUMBER
    out=update(sql)
    return out


def query_user_permium_time(PLATE_NUMBER):
    sql="SELECT  v.PLATE_NUMBER AS licenseNo,v.FRAME_NUMBER AS vinNo ,v.VEHICLE_TYPE AS CCardDetail,v.VEHICLE_MODEL  AS CModelCde, v.SEATING_CAPACITY AS  NSeatNum ,v.INITIAL_REGISTRATION_DATE AS enrollDate , v.CUST_NAME AS insuredName ,v.ENGINE_NUMBER AS engineNo ,CONCAT(v.CHINESE_BRAND,v.VEHICLE_MODEL) AS brandName FROM vehicle_info v WHERE   v.PLATE_NUMBER='%s' LIMIT 1"  % PLATE_NUMBER
    sql2="SELECT palte_number AS licenseNo,company_id AS COMPANY_ID ,end_time AS endDate FROM user_permium_time  WHERE palte_number='%s'" % PLATE_NUMBER
    company={

    }
    result = query(sql)
    result2=query(sql2)
    try:
        if len(result)!=0:
            result=result[0]
            result={field: result.get(field, '').decode() for field in result if isinstance(result.get(field, ''), str)}
            result['CCardDetail']=car_type[result['CCardDetail'].encode()]
            if len(result2)!=0:
                result2=result2[0]
                result2={field: result2.get(field, '').decode() for field in result2 if isinstance(result2.get(field, ''), str)}
                result['endDate']=result2['endDate'].split(" ")[0]
                result['COMPANY_ID']=result2['COMPANY_ID']
                result["identifyNumber"]=""
            else:
                result['endDate']=""
                result['COMPANY_ID']=""
                result["identifyNumber"]=""
            return  result
        else:
            return 0
    except Exception,e:
        return 0








if __name__ == "__main__":
    dt = {
        "licenseNo": "苏AN0Q84",
        "vinNo": "LVTDB14B7FC003044",
        "endDate": "2017-01-27",
        "CCardDetail": "K33",
        "brandName": "奇瑞SQR6451T217",
        "insuredName": "熊飞",
        "identifyNumber": "321183198903103659",
        "CUsageCde": "309001",
        "NNewPurchaseValue": "80910.00",
        "COMPANY_ID": "4",
        "insuredAddress": "句容市",
        "mobile": "13921551352",
        "enrollDate": "2015-01-29",
        "engineNo": "TAEM02094",
        "CModelCde": "",
        "NSeatNum": "5"}
    #inser_user_renewal(dt)
    #a=query_user_renewal("苏AB1P05")
    #b = {field: a.get(field, '').decode() for field in a if isinstance(a.get(field, ''), str)}
    #del_user_renewal("苏AN0Q83")
    print json.dumps(query_user_permium_time('苏AS008F'),ensure_ascii=False)
